SQL Query to Calculate Running Total in SQL Server

Question

Ravi Vishwakarma · Answer

To calculate a running total in SQL Server, you can use the SUM function along with the
OVER clause.and 
Here’s an example query that calculates a running total of the Amount column in a table called
Transactions:
Example Table Schema
Assume you have a table named Transactions with the following schema:
CREATE TABLE Transactions (
    TransactionID INT PRIMARY KEY,
    TransactionDate DATE,
    Amount DECIMAL(10, 2)
);
Insert data to Table
INSERT INTO Transactions (TransactionID, TransactionDate, Amount) VALUES
(1, '2023-01-01', 100.00),
(2, '2023-01-02', 150.00),
(3, '2023-01-03', 200.00),
(4, '2023-01-04', 250.00),
(5, '2023-01-05', 300.00),
(6, '2023-01-06', 350.00),
(7, '2023-01-07', 400.00),
(8, '2023-01-08', 450.00),
(9, '2023-01-09', 500.00),
(10, '2023-01-10', 550.00),
(11, '2023-01-11', 600.00),
(12, '2023-01-12', 650.00),
(13, '2023-01-13', 700.00),
(14, '2023-01-14', 750.00),
(15, '2023-01-15', 800.00),
(16, '2023-01-16', 850.00),
(17, '2023-01-17', 900.00),
(18, '2023-01-18', 950.00),
(19, '2023-01-19', 1000.00),
(20, '2023-01-20', 1050.00);
Sample Query
Here's how you can calculate the running total:
SELECT 
    TransactionID, 
    TransactionDate, 
    Amount, 
    SUM(Amount) OVER (ORDER BY TransactionDate) AS RunningTotal
FROM 
    Transactions
ORDER BY 
    TransactionDate;
Explanation:
SELECT TransactionID, TransactionDate, Amount: Selects the columns to display.SUM(Amount) OVER (ORDER BY TransactionDate) AS RunningTotal:
SUM(Amount) calculates the sum of the Amount column.OVER (ORDER BY TransactionDate) specifies the order in which the rows are processed. It calculates the running total for each row in the order of
TransactionDate.AS RunningTotal names the calculated column as RunningTotal.
FROM Transactions: Specifies the table to query.ORDER BY TransactionDate: Orders the final result set by TransactionDate.
This query will produce a result set with each row showing the TransactionID,
TransactionDate, Amount, and the RunningTotal up to that row, ordered by
TransactionDate.
Or
SELECT  
    TransactionDate, 
    SUM(Amount) AS RunningTotal
FROM Transactions
GROUP BY TransactionDate
This query will produce a result set with each row showing the TransactionDate, and the
RunningTotal up to that row.
Read more
Describe Common Table Expressions (CTEs) in SQL server.
How to Join Multiple Tables and Retrieve Specific Columns in SQL Server?
Implement row-level security in SQL Server to restrict access to data to users.

forum

SQL Query to Calculate Running Total in SQL Server

Anubhav Kumar

Can you answer this question?

1 Answers

Example Table Schema

Insert data to Table

Sample Query

Liked By